# -*- coding: utf-8 -*-
#
# The Philatelist program is a fork of the OpenNumismat program
# Copyright (c) 2011 - 2024 Vitaly Ignatov (OpenNumismat) <opennumismat@gmail.com>
# Copyright (c) 2021 - 2024 Victor Nabatov (Philatelist) <nabatov.victor@gmail.com>

from PySide6.QtSql import QSqlQuery
from PySide6.QtWidgets import QMessageBox

from Philatelist.Collection.Collection import CollectionSettings
from Philatelist.Collection.CollectionFields import ImagesFields, TraitsImagesFields
from Philatelist.Tools.Gui import ProgressDialog
from Philatelist import version

class SynchronizeCollections():

    def __init__(self, collection):
        self.collection = collection

    def synchronize(self, fileName):
        query = QSqlQuery(self.collection.db)
        query.prepare("ATTACH ? AS src")
        query.addBindValue(fileName)
        res = query.exec_()
        if not res:
            QMessageBox.critical(
                self.collection.parent(),
                self.collection.tr("Synchronizing"),
                self.collection.tr("Can't open collection:\n%s") %
                query.lastError().text()
            )
            return

        sql = "SELECT value FROM src.settings WHERE title='Type'"
        query = QSqlQuery(sql, self.collection.db)
        query.first()
        type_ = query.record().value(0)
        if type_ != version.AppName:
            QMessageBox.critical(
                self.collection.parent(),
                self.collection.tr("Synchronizing"),
                self.collection.tr("Collection %s in wrong format") % fileName
            )
            return

        sql = "SELECT value FROM src.settings WHERE title='Version'"
        query = QSqlQuery(sql, self.collection.db)
        query.first()
        ver = query.record().value(0)
        if int(ver) != CollectionSettings.Default['Version']:
            QMessageBox.critical(
                self.collection.parent(),
                self.collection.tr("Synchronizing"),
                self.collection.tr("Collection %s is in old format.\n(Try to open it before merging.)") % fileName
            )
            return

        query = QSqlQuery("SELECT COUNT(*) FROM src.stamps", self.collection.db)
        query.first()
        count = query.record().value(0)

        progressDlg = ProgressDialog(
            self.collection.tr("Synchronizing"),
            self.collection.tr("Cancel"),
            count,
            self.collection.parent()
        )
        fields_query = QSqlQuery("PRAGMA table_info(stamps)", self.collection.db)
        fields_query.exec_()
        fields = []
        while fields_query.next():
            fields.append(fields_query.record().value(1))

        fields.remove('id')
        sql_fields = ','.join(fields)

        inserted_count = 0
        updated_count = 0
        query = QSqlQuery("SELECT DISTINCT catalognum1 FROM src.stamps", self.collection.db)
        while query.next():
            progressDlg.step()
            if progressDlg.wasCanceled():
                break

            self.collection.db.transaction()
            sql = "SELECT 1 FROM stamps WHERE catalognum1=? LIMIT 1"
            select_query = QSqlQuery(sql, self.collection.db)
            select_query.addBindValue(query.record().value(0))
            select_query.exec_()
            if select_query.first():
                dst_fields = ImagesFields +  TraitsImagesFields + ('id', 'image', 'sort_id')
                sql_dst_fields = ','.join(['stamps.%s AS stamps_%s' % (f, f) for f in dst_fields])
                sql_src_fields = ','.join(['src_stamps.%s' % f for f in fields])
                sql = "SELECT %s, %s FROM stamps\
                    INNER JOIN src.stamps src_stamps ON stamps.id=src_stamps.id\
                    WHERE src_stamps.catalognum1=? AND\
                          src_stamps.catalognum1>stamps.catalognum1" % (sql_src_fields, sql_dst_fields)

                sel_query = QSqlQuery(sql, self.collection.db)
                sel_query.addBindValue(query.record().value(0))
                sel_query.exec_()
                if sel_query.first():
                    sql = "UPDATE stamps SET %s WHERE id=?" % ','.join(['%s=?' % f for f in fields])
                    up_query = QSqlQuery(sql, self.collection.db)
                    for field in fields:
                        if field == 'image':
                            img_id = sel_query.record().value(field)
                            old_img_id = sel_query.record().value('stamps_image')
                            if img_id and old_img_id:
                                sql = "UPDATE images SET image=(SELECT image FROM src.images WHERE id=?) WHERE id=?"
                                img_query = QSqlQuery(sql, self.collection.db)
                                img_query.addBindValue(img_id)
                                img_query.addBindValue(old_img_id)
                                img_query.exec_()

                            elif img_id:
                                sql = "INSERT INTO images (image) SELECT image FROM src.images WHERE id=?"
                                img_query = QSqlQuery(sql, self.collection.db)
                                img_query.addBindValue(img_id)
                                img_query.exec_()
                                img_id = img_query.lastInsertId()

                            elif old_img_id:
                                sql = "DELETE FROM images WHERE id=?"
                                img_query = QSqlQuery(sql, self.collection.db)
                                img_query.addBindValue(old_img_id)
                                img_query.exec_()
                                img_id = None

                            up_query.addBindValue(img_id)

                        elif field == 'sort_id':
                            up_query.addBindValue(sel_query.record().value('stamps_sort_id'))
                        else:
                            up_query.addBindValue(sel_query.record().value(field))

                    up_query.addBindValue(sel_query.record().value('stamps_id'))
                    up_query.exec_()
                    updated_count += 1
            else:
                sql = "SELECT %s FROM src.stamps WHERE catalognum1=?" % sql_fields
                sel_query = QSqlQuery(sql, self.collection.db)
                sel_query.addBindValue(query.record().value(0))
                sel_query.exec_()
                while sel_query.next():
                    sql = "INSERT INTO stamps (%s) VALUES (%s)" % (sql_fields, ','.join(['?'] * len(fields)))
                    ins_query = QSqlQuery(sql, self.collection.db)
                    for field in fields:
                        if field == 'image':
                            old_img_id = sel_query.record().value(field)
                            if old_img_id:
                                sql = "INSERT INTO images (image) SELECT image FROM src.images WHERE id=?"
                                img_query = QSqlQuery(sql, self.collection.db)
                                img_query.addBindValue(old_img_id)
                                img_query.exec_()

                                img_id = img_query.lastInsertId()
                            else:
                                img_id = None

                            ins_query.addBindValue(img_id)

                        elif field == 'sort_id':
                            sort_query = QSqlQuery("SELECT MAX(sort_id) FROM stamps", self.collection.db)
                            sort_query.first()
                            sort_id = sort_query.record().value(0)
                            if not sort_id:
                                sort_id = 0
                            ins_query.addBindValue(sort_id + 1)
                        else:
                            ins_query.addBindValue(sel_query.record().value(field))

                    ins_query.exec_()
                    inserted_count += 1

            self.collection.db.commit()

        query = QSqlQuery("DETACH src", self.collection.db)
        query.exec_()

        progressDlg.reset()
        if inserted_count or updated_count:
            text = self.collection.tr("Inserted %d stamps, updated %d stamps.\nThe application needs to be restarted.") % (inserted_count, updated_count)
            QMessageBox.information(self.collection.parent(), self.collection.tr("Synchronizing"), text)
            # TODO: refresh
            self.collection.parent().restart()
        else:
            text = self.collection.tr("Collections looks like identical")
            QMessageBox.information(self.collection.parent(), self.collection.tr("Synchronizing"), text)
